#coding=utf-8
import sys
import os
from string import Template
from common.DBConnectionInfo import DBConnectionInfo as DBConn
from common.DBCursorFactory import DBCursorFactory as DBCursor


def getConn(sqlType, connectionInfo ):
	return DBCursor.getConnection(sqlType, connectionInfo)
	
def getConnInfoDic():
	return DBConn.Connections
	
def execSqlCmd(sqlCmd, paramsDict={},sqlType='mssql',dbKey='BI_ODS_READONLY'):
	connections = getConnInfoDic()
	
	if connections.has_key(dbKey):
		
		try:
			if sqlCmd == '':
				raise NameError("sql cmd text can not be empty!")
			else:
				tpl =Template(sqlCmd)
				sqlCmd=tpl.safe_substitute(paramsDict)
			
			conn = getConn(sqlType,connections[dbKey])
			cur = conn.cursor()
			
			print 'Final SQL Cmd: ',sqlCmd
			cur.execute(sqlCmd)
			conn.commit()
		except Exception as e:
			print str(e)
			raise e
	else:
		errormsg = 'The dbkey %s has not configured in the conf file or is not exists.'%dbKey
		raise NameError(errormsg)

def execSqlCmd2Result(sqlCmd, paramsDict={},sqlType='mssql',dbKey='BI_ODS_READONLY'):
	connections = getConnInfoDic()
	result = None
	
	if connections.has_key(dbKey):
		try:
			if sqlCmd == '':
				raise NameError("sql cmd text can not be empty!")
			else:
				tpl =Template(sqlCmd)
				sqlCmd=tpl.safe_substitute(paramsDict)
				
			conn = getConn(sqlType,connections[dbKey])
			cur = conn.cursor()
			print 'Final SQL Cmd: ',sqlCmd
			cur.execute(sqlCmd)
			result =  cur.fetchall()
		except Exception as e:
			raise e
	else:
		errormsg = 'The dbkey %s has not configured in the conf file or is not exists.'%dbKey
		raise NameError(errormsg)
		
	return result

def readFile(sqlFile):
	if not os.path.exists(sqlFile):
		raise NameError("The SQL file %s is not exists."%sqlFile)
	sql_str = ''	
	with open(sqlFile,'r') as sql_file:
		for line in sql_file.readlines():
			sql_str = sql_str+line
        #print "line: ",line 
	return sql_str
	
def execSqlFile(sqlFile, paramsDict={},sqlType='mssql',dbKey='BI_ODS_READONLY'):
	sqlCmd = readFile(sqlFile)
	
	execSqlCmd(sqlCmd,paramsDict,sqlType,dbKey)

	
def execSqlFile2Result(sqlFile, paramsDict={},sqlType='mssql',dbKey='BI_ODS_READONLY'):
	sqlCmd = readFile(sqlFile)
	
	return execSqlCmd2Result(sqlCmd,paramsDict,sqlType,dbKey)
